package MydbQA::Command::Columns;

use strict;
use warnings;

use base 'MydbQA::Command::Base';
use Carp;
use HTML::Template;
use Data::Dumper;

use List::Util qw(min max sum);

sub run {
      my $self = shift;

      my $tree = MySQL::Query->new( sql => $self->query );
      $self->analyse_columns( $tree );
      
      #print Dumper $self->{_data}->{columns}; #die;
      
      $self->{html} ? $self->as_html : $self->as_text;
}

sub as_html {
      my ($self , $data) = @_;
        
      my $tmpl = HTML::Template->new( filename => "../share/tmpl/columns.html" , die_on_bad_params => 0 );
      $tmpl->param(columns => $self->{_data}->{columns});
      $tmpl->param(nohighlight => 1) if $self->{nohighlight};
      
      my $path = $self->{output};
      
      open (FH , "+>$path/analyze_columns.html") || die print $!;
            print FH $tmpl->output;
      close FH;
        
      print "Columns report generated in html file: $path/analyze_columns.html"; 
}

sub as_text {
      print "Text report is not implemented yet";
}



# handle databases
sub used_columns {
    my $self = shift;
    my $query = shift;

    my (%alias, @identifiers);
    $query->walk( sub {
        my $rule = shift;
        return 1 unless $rule->isa('DBIx::MyParsePP::Rule');

        my $rule_name = $rule->name;
        if ( $rule_name eq 'simple_ident' ) {
            my @ident = map $_->value, grep $_->type eq 'IDENT', reverse $rule->shrink->children;
            if ( $ident[2] ) {
                warn "don't know what to do with database in ident";
            } elsif ( !$ident[1] ) {
                warn "Couldn't handle column identifiers without aliases :(";
            } else {
                splice @ident, 2, 0, ($alias{ $ident[1] } ||= []);
                push @identifiers, \@ident;
            }
            return 0;
        }
        elsif ( $rule_name eq 'table_factor' ) {
            my ($first_child) = $rule->children;
            if ( $first_child->isa('DBIx::MyParsePP::Rule') && $first_child->name eq 'table_ident' ) {
                my ($table, $alias) =
                    map $_->value, map ref($_) eq 'ARRAY'? @$_ : $_,
                    map $_->extract('IDENT'), $rule->shrink;
                $alias{ $alias || $table } ||= [];
                @{ $alias{ $alias || $table } } = ($table, $alias);
                return 0;
            }
        }
        return 1;
    } );
    $_->[2] = $_->[2][0] foreach @identifiers;

    die "Unknown identifier ". $_->[1] .".". $_->[0]
        foreach grep !$_->[2], @identifiers;
    $_ = { column => $_->[0], alias => $_->[1], table => $_->[2], database => $_->[3] || $self->{db} }
        foreach @identifiers;

    return @identifiers;
}

sub analyse_columns {
      my ($self, $query) = @_;
      foreach ($self->used_columns( $query )) {
            #print Dumper $_;
            my $analyse = $self->analyse_column( %$_ );
            $analyse->{PREFIX_SELECTIVITY} = join "<br />" , @{$analyse->{PREFIX_SELECTIVITY}};
            my %temp = (%$_ , %{$analyse});
            push @{$self->{_data}->{columns}} , \%temp;
      }
}

my %type_handlers = (
    varchar  => 'text',
    char     => 'text',
    text     => 'text',
    longtext => 'text',

    binary   => 'binary',
    blob     => 'binary',

    integer => 'integer',
    int     => 'integer',
);

sub analyse_column {
    my ($self, %info) = @_;

    %info = $self->column_meta( %info );
    die "no info" unless keys %info;
    
    my $type = lc $info{'DATA_TYPE'};
    $self->analyse_total( \%info );
    $self->analyse_nulls( \%info );
    $self->analyse_distinct( \%info );
    $self->analyse_prefix_selectivity( \%info );
    
    return \%info;
    
}

sub column_meta {
    my ($self, %info) = @_;
    my $res = $self->dbh->selectrow_hashref(
        "SELECT * FROM INFORMATION_SCHEMA.COLUMNS
            WHERE table_name = ? AND column_name = ? AND table_schema = ?",
        {},
        grep defined && length, @info{'table', 'column', 'database'}
    );
    return () unless $res;
    return %$res;
}

sub analyse_total {
      my ($self, $info) = @_;
      my $dbh = $self->dbh;
      my $query = 
            "SELECT COUNT(1) FROM "
            . $dbh->quote_identifier( undef, $info->{'TABLE_SCHEMA'}, $info->{'TABLE_NAME'} );
      ($info->{'TOTAL_COUNT'}) = $dbh->selectrow_array( $query );
}

sub analyse_nulls {
      my ($self, $info) = @_;
      if ( $info->{'IS_NULLABLE'} =~ /YES/i ) {
            $info->{'IS_NULLABLE'} = 1;
      } else {
            $info->{'IS_NULLABLE'} = 0;
      }
      return unless $info->{'IS_NULLABLE'};

      my $dbh = $self->dbh;
      my $query = 
            "SELECT COUNT(1) FROM "
            . $dbh->quote_identifier( undef, $info->{'TABLE_SCHEMA'}, $info->{'TABLE_NAME'} )
            ." WHERE ". $dbh->quote_identifier( $info->{'COLUMN_NAME'} ) ." IS NULL";
      ($info->{'NULLS_COUNT'}) = $dbh->selectrow_array( $query );
}

sub analyse_distinct {
      my ($self, $info) = @_;

      my $dbh = $self->dbh;
      my $query = 
            "SELECT COUNT(1) counter FROM "
            . $self->qi( undef, $info->{'TABLE_SCHEMA'}, $info->{'TABLE_NAME'} )
            ." WHERE ". $self->qi( $info->{'COLUMN_NAME'} ) ." IS NOT NULL"
            ." GROUP BY ". $self->qi( $info->{'COLUMN_NAME'} );
      $query = "SELECT COUNT(1), MIN(c.counter), MAX(c.counter), AVG(c.counter)"
            ." FROM ( $query ) c";
      my ($distinct, $min, $max, $avg) = $dbh->selectrow_array( $query );
      if ( $min == 1 && $min == $max ) {
            $info->{'IS_DISTINCT'} = 1;
      }
      else {
            $info->{'IS_UNIFORM'} = $min if $min == $max;
            $info->{'DISTINCT_COUNT'} = $distinct;
            $info->{'DISTINCT_GROUP_MIN'} = $min;
            $info->{'DISTINCT_GROUP_MAX'} = $max;
            $info->{'DISTINCT_GROUP_AVG'} = $avg;
      }
}

sub analyse_prefix_selectivity {
      my ($self, $info) = @_;

      my $max_length = $info->{'CHARACTER_MAXIMUM_LENGTH'};
      return unless $max_length;
      return unless $info->{'TOTAL_COUNT'} > 1;

      my $dbh = $self->dbh;
      my @res = ();
      for my $l ( 1 .. min 20, $max_length ){
            my $query = 
                  "SELECT COUNT(DISTINCT SUBSTR(". $self->qi( $info->{'COLUMN_NAME'} )
                  .", 0, $l)) FROM "
                  . $self->qi( undef, $info->{'TABLE_SCHEMA'}, $info->{'TABLE_NAME'} )
                  ." WHERE ". $self->qi( $info->{'COLUMN_NAME'} ) ." IS NOT NULL";
            my ($count) = $dbh->selectrow_array( $query ); 
            push @res, ($info->{'TOTAL_COUNT'} - $count)/$info->{'TOTAL_COUNT'};
      }
      $info->{'PREFIX_SELECTIVITY'} = \@res;
}

sub qi { return (shift)->dbh->quote_identifier( @_ ) }

sub analyze_integer_column {
}

1;
